package com.zygh.policeasset.mapper;

import com.zygh.policeasset.pojo.Project;
import com.zygh.policeasset.pojo.ProjectNameType;
import com.zygh.policeasset.pojo.Section;
import com.zygh.policeasset.pojo.Settlement;
import com.zygh.policeasset.pojo.vo.ProjectInfoVO;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

@Mapper
public interface SettlementMapper {
    @Select({"<script>", "select p.*,cd.initial_type,cd.final_type,cd.progress,sec.section_name as sectionName from completed cd left join settlement s on cd.section_id = s.section_id LEFT JOIN section sec ON cd.section_id = sec.id left join project p on cd.project_no = p.project_no left join contract c on cd.section_id = c.section_id left join company cp on c.company_id = cp.id left join audit_flow af on cd.flow_no = af.flow_no where af.app_status = 2 and s.section_id is null <if test='userId != null '>and af.applicant = #{userId} </if>order by cd.id desc </script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime"), @Result(property = "initialType", column = "initial_type"), @Result(property = "finalType", column = "final_type")})
    List<Project> getProject(@Param("userId") Integer num);

    @Select({"<script>", "select sn.*,p.name as project_name,cd.initial_type,cp.company_name,c.money from completed cd left join settlement s on cd.section_id = s.section_id left join section sn on cd.section_id = sn.id left join contract c on cd.section_id = c.section_id left join company cp on c.company_id = cp.id left join project p on s.project_no = p.project_no left join audit_flow af on cd.flow_no = af.flow_no where af.app_status = 2 and s.section_id is null and cd.final_type = '已通过' and cd.project_no = #{projectNo} <if test='userId != null '>and af.applicant = #{userId} </if>order by sn.id desc </script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "projectName", column = "project_name"), @Result(property = "tenderId", column = "tender_id"), @Result(property = "sectionName", column = "section_name"), @Result(property = "sectionTime", column = "section_time"), @Result(property = "assectStatus", column = "assect_status"), @Result(property = "companyName", column = "company_name"), @Result(property = "initialType", column = "initial_type")})
    List<Section> getAdd(@Param("projectNo") String str, @Param("userId") Integer num);

    @Insert({"insert into settlement (project_no,audit_time,create_time,description,section_id,increase_or_decrease,amount,audit_unit,audit_amount,data_labels_id,flow_no) values (#{projectNo},#{auditTime},#{createTime},#{description},#{sectionId},#{increaseOrDecrease},#{amount},#{auditUnit},#{auditAmount},#{dataLabelsId},#{flowNo})"})
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    int add(Settlement settlement);

    @Select({"<script>", "select s.*,cp.company_name as winning_company," +
            "ct.money as winning_amount,p.name as project_name,sn.section_name," +
            "ct.name,af.approval_steps_id as approvalStepsId,af.app_status," +
            "u.name as userName from settlement s " +
            "left join contract ct on s.section_id = ct.section_id " +
            "left join company cp on ct.company_id = cp.id " +
            "left join section sn on s.section_id = sn.id " +
            "left join project p on s.project_no = p.project_no " +
            "left join audit_flow af on s.flow_no = af.flow_no " +
            "left join user u on af.applicant = u.id " +
            "<where>  " +
            "<if test='name != null and name.length &gt; 0'>and p.name like '%${name}%' </if>" +
            "<if test='category != null and category &gt; 0'>and p.category=#{category} </if>" +
            "<if test='companyId != null and companyId != 0 '>and cp.id = #{companyId} </if>" +
            "<if test='time1 != null and time1.length &gt; 0 and time2 != null and time2.length &gt; 0'>and s.audit_time between  #{time1} and #{time2} </if>" +
            "<if test='userId != null '> and af.applicant=#{userId} </if>" +
            "<if test=' status != null'> and af.app_status = #{status} </if>" +
            "</where>" +
            "order by s.id desc " +
            "</script>"})
    @Results({@Result(property = "projectNo", column = "project_no"),
            @Result(property = "projectName", column = "project_name"),
            @Result(property = "auditTime", column = "audit_time"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "sectionId", column = "section_id"),
            @Result(property = "sectionName", column = "section_name"),
            @Result(property = "contractName", column = "name"),
            @Result(property = "winningCompany", column = "winning_company"),
            @Result(property = "winningAmount", column = "winning_amount"),
            @Result(property = "increaseOrDecrease", column = "increase_or_decrease"),
            @Result(property = "auditUnit", column = "audit_unit"),
            @Result(property = "auditAmount", column = "audit_amount"),
            @Result(property = "sectionName", column = "section_name"),
            @Result(property = "contractName", column = "name"),
            @Result(property = "flowNo", column = "flow_no"),
            @Result(property = "appStatus", column = "app_status")})
    List<Settlement> findAll(@Param("name") String str, @Param("status") Integer num, @Param("companyId") Integer num2, @Param("time1") String str2, @Param("time2") String str3, @Param("category") Integer num3, @Param("userId") Integer num4);

    @Select({"select count(*) from settlement t  where app_status = 1"})
    Integer findCount();

    @Select({"select count(*) from settlement s  left join new_approval_process nap on s.project_no = nap.build_num and s.section_id = nap.section_id and nap.id =(SELECT id  FROM new_approval_process na WHERE nap.build_num = s.project_no and nap.section_id = s.section_id ORDER BY id DESC LIMIT 1)where nap.user_id = #{userId} and nap.approval_status = 1 "})
    Integer findAssetsAppCount(Integer num);

    @Select({"select s.*,cp.company_name as winning_company,ct.money as winning_amount,p.name as project_name,sn.section_name,ct.name,af.app_status from settlement s left join contract ct on s.section_id = ct.section_id left join company cp on ct.company_id = cp.id left join section sn on s.section_id = sn.id left join project p on s.project_no = p.project_no left join audit_flow af on s.flow_no = af.flow_no where s.project_no=#{no} order by s.id desc "})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "projectName", column = "project_name"), @Result(property = "auditTime", column = "audit_time"), @Result(property = "createTime", column = "create_time"), @Result(property = "sectionId", column = "section_id"), @Result(property = "sectionName", column = "section_name"), @Result(property = "contractName", column = "name"), @Result(property = "winningCompany", column = "winning_company"), @Result(property = "winningAmount", column = "winning_amount"), @Result(property = "increaseOrDecrease", column = "increase_or_decrease"), @Result(property = "auditUnit", column = "audit_unit"), @Result(property = "auditAmount", column = "audit_amount"), @Result(property = "appStatus", column = "app_status")})
    List<Settlement> findByProject(String str);

    @Select({"select s.*,cp.company_name as winning_company,ct.money as winning_amount,p.name as project_name,pr.status as status,pr.opinion as opinion from settlement s left join processrecord pr on s.id=pr.project_id and pr.process_name='settlement' left join project p on s.project_no = p.project_no left join contract ct on s.section_id = ct.section_id left join company cp on ct.company_id = cp.id where pr.status=#{status} order by s.id desc "})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "projectName", column = "project_name"), @Result(property = "auditTime", column = "audit_time"), @Result(property = "createTime", column = "create_time"), @Result(property = "sectionId", column = "section_id"), @Result(property = "winningCompany", column = "winning_company"), @Result(property = "winningAmount", column = "winning_amount"), @Result(property = "increaseOrDecrease", column = "increase_or_decrease"), @Result(property = "auditUnit", column = "audit_unit"), @Result(property = "auditAmount", column = "audit_amount")})
    List<Settlement> findByStatus(int i);

    @Select({"select s.*,cp.company_name as winning_company,ct.money as winning_amount,p.name as project_name,st.section_name,af.approval_steps_id as approvalStepsId,af.app_status,u.name as userName from settlement s left join project p on s.project_no = p.project_no left join contract ct on s.section_id = ct.section_id left join company cp on ct.company_id = cp.id left join audit_flow af on s.flow_no = af.flow_no left join user u on af.applicant = u.id left join section st on s.section_id = st.id and s.section_id = st.id where s.id = #{id}"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "projectName", column = "project_name"), @Result(property = "auditTime", column = "audit_time"), @Result(property = "createTime", column = "create_time"), @Result(property = "sectionId", column = "section_id"), @Result(property = "sectionName", column = "section_name"), @Result(property = "winningCompany", column = "winning_company"), @Result(property = "winningAmount", column = "winning_amount"), @Result(property = "increaseOrDecrease", column = "increase_or_decrease"), @Result(property = "auditUnit", column = "audit_unit"), @Result(property = "auditAmount", column = "audit_amount"), @Result(property = "flowNo", column = "flow_no"), @Result(property = "appStatus", column = "app_status")})
    Settlement selectById(Integer num);

    @Update({"update settlement set project_no = #{projectNo},audit_time =#{auditTime},create_time = #{createTime},description=#{description},section_id=#{sectionId},increase_or_decrease=#{increaseOrDecrease},amount=#{amount},audit_unit=#{auditUnit},audit_amount=#{auditAmount} where id = #{id}"})
    int update(Settlement settlement);

    @Delete({"delete from settlement where id = #{id} "})
    int delete(Integer num);

    @Update({"update settlement set app_status=#{status} where id=#{id}"})
    Integer updateStatus(int i, int i2);

    @Select({"SELECT DISTINCT p.name,p.project_no  FROM settlement c LEFT JOIN  project p ON c.project_no= p.project_no left join audit_flow a on c.flow_no=a.flow_no WHERE a.app_status = 2 "})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "projectName", column = "name")})
    List<ProjectNameType> selectBytype();

    @Select({"SELECT s.section_name  FROM settlement c LEFT JOIN  section s ON c.section_id= s.id WHERE c.project_no = #{projectNo} "})
    @Results({@Result(property = "sectionName", column = "section_name")})
    List<ProjectNameType> findSectionByType(@Param("projectNo") String str);

    @Select({"<script>SELECT t.*,p.name as project_name,s.`section_name`,co.company_name,cs.money   FROM settlement t LEFT JOIN  project p ON t.project_no= p.project_no LEFT JOIN category c ON p.category = c.id  LEFT JOIN `section` s  ON  t.`section_id`=s.`id` LEFT JOIN `contract` cs ON  cs.`project_no`=t.`project_no` and  cs.`section_id`=t.`section_id`LEFT JOIN `company` co ON  cs.`company_id`=co.`id` left join audit_flow af on t.flow_no = af.flow_no <where>af.app_status = 2 and p.category=#{id}<if test='name != null and name.length &gt; 0'>and p.name like '%${name}%' </if><if test='companyId != null and companyId != 0'>and cs.company_id = #{companyId} </if><if test='time1 != null and time2 != null and time1.length &gt; 0 and time2.length &gt; 0'>and t.audit_time between #{time1} and  #{time2} </if></where></script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "projectName", column = "project_name"), @Result(property = "auditTime", column = "audit_time"), @Result(property = "createTime", column = "create_time"), @Result(property = "sectionId", column = "section_id"), @Result(property = "winningCompany", column = "company_name"), @Result(property = "winningAmount", column = "money"), @Result(property = "increaseOrDecrease", column = "increase_or_decrease"), @Result(property = "auditUnit", column = "audit_unit"), @Result(property = "auditAmount", column = "audit_amount"), @Result(property = "sectionName", column = "section_name")})
    List<Settlement> toExcel(Integer num, String str, String str2, String str3, String str4);

    @Update({"update settlement set asset_new_status=#{assetNewStatus} where project_no = #{projectNo} and section_id = #{sectionId}"})
    int updateStatusAssets(@Param("assetNewStatus") String str, @Param("projectNo") String str2, @Param("sectionId") Integer num);

    @Update({"update settlement set start_provision_date=#{startProvisionDate},certificate_num=#{certificateNum} where project_no = #{projectNo} and section_id = #{sectionId}"})
    int updateSPD(@Param("startProvisionDate") String str, @Param("certificateNum") String str2, @Param("projectNo") String str3, @Param("sectionId") Integer num);

    @Update({"update settlement set asset_new_status = #{assetApprovalStatus} where project_no = #{projectNo} and section_id = #{sectionId} "})
    Integer updateZNJCAssetApprovalStatus(Integer num, String str, Integer num2);

    @Select({"select count(*) from settlement where asset_new_status in (1,2)  and project_no = #{projectNo} and section_id = #{sectionId} "})
    Integer check(String str, Integer num);

    @Select({"<script>", "select s.id,s.project_no as buildNum,p.name as projectName,sn.section_name as sectionName,s.section_id as sectionId,s.company_id as companyId from contract s left join section sn on s.section_id = sn.id left join project p on s.project_no = p.project_no <where>  p.pid != 0 <if test='name != null and name.length &gt; 0'>and p.name like concat('%',#{name} ,'%') </if><if test='buildNum != null and buildNum.length &gt; 0'>and s.project_no = #{buildNum} </if><if test='sectionId != null'>and s.section_id = #{sectionId} </if><if test='category != null'>and p.category = #{category} </if><if test='unitId != null'>and s.company_id = #{unitId} </if></where> order by s.id desc </script>"})
    List<ProjectInfoVO> getProjectInfo(@Param("name") String str, @Param("buildNum") String str2, @Param("sectionId") Integer num, @Param("category") Integer num2, @Param("unitId") Integer num3);

    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "sectionId", column = "section_id")})
    @Select({"select * from settlement where flow_no  = #{flowNo}"})
    Settlement selectIdByFlowNo(String str);

    @Select({"select c.id,u.id as userId  from settlement c left join contract ct on c.project_no =ct.project_no and c.section_id=ct.section_id left join user u on ct.company_id = u.unit_id where c.asset_new_status = 0"})
    List<Settlement> assets();
}